package com.flong.modules.service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.flong.modules.AppException;
import com.flong.modules.BaseService;
import com.flong.modules.model.oracle.UserTables;
import com.flong.modules.utils.DBHelper;
import com.flong.modules.utils.ObjectUtil;

/**
 * @Description	TODO
 * @ClassName	TableService
 * @Date		2017年3月1日 上午10:48:44
 * @Author		liangjilong
 * @Copyright (c) All Right Reserved , 2017.
 */
	
@SuppressWarnings("all")
public class TableService extends BaseService{
	
	public static final List<String> IGNORE_TABLE_PREFIX = new ArrayList<String>();
	
	/**
	 * @Description  获取数据库所有表
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午2:30:56
	 * @throws Exception 		参数
	 * @return 		List<String> 返回类型
	 */
	public static List<UserTables> getTables() throws Exception {
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = getUserTablesOracleSQL() +" from user_tables ";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//sql = " select * from user_tables ";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			sql = " select table_name from information_schema.tables ";
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		return retTableList(sql, conn);
	}
	
	
	/**
	 * @Description 根据前缀或者后缀获取表
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午2:30:16
	 * @param suffix
	 * @throws Exception 		参数
	 * @return 		List<String> 返回类型
	 */
	public static List<UserTables> getTablesBySuffix(String suffix) throws Exception {
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = getUserTablesOracleSQL() + " from user_tables w where w.table_name like '%'||upper('"+suffix+"')||'%'";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//sql = " select * from user_tables ";//SQLSERVER是用+链接
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			sql = " select table_name from information_schema.tables where table_schema concat('%','"+suffix+"','%') order by table_name ";
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		return retTableList(sql, conn);
	}


	/**
	 * @Description 根据表名获取信息
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午2:30:16
	 * @param suffix
	 * @throws Exception 		参数
	 * @return 		List<String> 返回类型
	 */
	public static List<UserTables> getTablesByName(String tableName) throws Exception {
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = getUserTablesOracleSQL() + " from user_tables w where upper(w.table_name) = upper('"+tableName+"')";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//sql = " select * from user_tables ";//SQLSERVER是用+链接
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			sql = " select table_name from information_schema.tables where table_schema='"+tableName+"' order by table_name ";
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		return retTableList(sql, conn);
	}
	 
	/**
	 * @Description 返回表的列表
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午2:31:38
	 * @param sql
	 * @param conn
	 * @return
	 * @return 		List<String> 返回类型
	 * @throws Exception 
	 */
	private static List<UserTables> retTableList(String sql, Connection conn) {
		List<UserTables> convertToEntitys = null;
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			convertToEntitys = DBHelper.convertToEntitys(rs, UserTables.class);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.release(conn, psmt, rs);
		}
		return convertToEntitys;
	}
	
	/***
	 * 获取表的常量名，般是在数据库建表的时候，写的注释..
	 * @param tableName
	 * @return
	 */
	private String getTableConstantName(String tableName) {
		String tableConstantName = tableName.toUpperCase();
		for (String item : IGNORE_TABLE_PREFIX) {
			tableConstantName = tableConstantName.replaceAll("^" + item.toUpperCase(), "");
		}
		return tableConstantName;
	}
	
	
	/**
	 * @Description 根据表获取数量,返回1表示存在，-1代表不存在
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午3:00:53
	 * @param 		tableName
	 * @return 		int 返回类型
	 */
	public static int getCountByTableName(String tableName)throws Exception{
		int result = -1;
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = " select count(*) as num from user_tables w where upper(w.table_name)=upper('"+tableName+"')";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//sql = " select * from user_tables ";//SQLSERVER是用+链接
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			sql = " select count(*) as num from information_schema.tables where upper(table_schema)=concat('',upper('"+tableName+"'),'') order by table_name ";
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		result = getNumFieldValue(result, sql);
		return result;
	}
	
	/**
	 * @Description 获取Oracle的UserTables的查询sql
	 * @Author		liangjilong
	 * @Date		2017年3月1日 上午9:59:44
	 * @return 		参数
	 * @return 		String 返回类型
	 */
	private static String getUserTablesOracleSQL(){
		StringBuffer buffer = new StringBuffer();
		buffer.append("select table_name, tablespace_name, cluster_name, iot_name, status, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extents,");
		buffer.append("max_extents, pct_increase, freelists, freelist_groups, logging, backed_up, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len,");
		buffer.append("avg_space_freelist_blocks, num_freelist_blocks, degree, instances, cache, table_lock, sample_size, last_analyzed, partitioned, iot_type, temporary,");
		buffer.append("secondary, nested, buffer_pool, flash_cache, cell_flash_cache, row_movement, global_stats, user_stats, duration, skip_corrupt, monitoring, ");
		buffer.append("cluster_owner, dependencies, compression, compress_for, dropped, read_only, segment_created, result_cache");
		return buffer.toString();
	}
	
	
	
	public static void main(String[] args) throws Exception{
		  List<UserTables> tables = getTables();
		  for(UserTables t:tables){
			  System.out.println(t.getTable_name());
		  }
		
	}
	
 
	

	

}
